package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

import co.gov.medellin.ssti.sub.model.dao.ModificarBOTDAO;
import co.gov.medellin.ssti.sub.model.vo.AtributoVO;
import co.gov.medellin.ssti.sub.model.vo.ModificarBOTVO;

public class ModificarBOTDAOImpl2 extends NamedParameterJdbcDaoSupport
		implements ModificarBOTDAO {

	// consultas
	private static final String SELECT_ATRIBUTOS_SUBPROGRAMA = "SELECT CODIGO, NOMBRE, TIPO_DATO, TIPO_ATRIBUTO "
			+ "FROM TCS_BEN_SUBP_ATR_BENEFICIARIO WHERE SBPR_CODIGO = :SBPR_CODIGO "
			+ "AND VIGENCIA = 'A' AND CAMPO_A2 IS NULL ";

	private static final String SELECT_SEDE_OTORGAMIENTO = "SELECT CODSEDE FROM VCS_BENEFICIOS_OTORGADOS "
			+ "WHERE CODBOT = :CODBOT ";

	private static final String SELECT_BENEFICIARIO_VALOR_NUM_ATR = "SELECT VALOR_NUM_ATR "
			+ " valor FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ "WHERE BEN_CODIGO = :BEN_CODIGO AND SAB_CODIGO = :SAB_CODIGO ";

	private static final String SELECT_BENEFICIARIO_VALOR_FEC_ATR = "SELECT TO_CHAR(VALOR_FEC_ATR,'DD/MM/RRRR') "
			+ " valor, codigo FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ "WHERE BEN_CODIGO = :BEN_CODIGO AND SAB_CODIGO = :SAB_CODIGO ";

	private static final String SELECT_BENEFICIARIO_VALOR_CAR_ATR = "SELECT VALOR_CAR_ATR "
			+ " valor, codigo FROM TCS_BEN_BENEFICIARIO_ATRIBUTO "
			+ "WHERE BEN_CODIGO = :BEN_CODIGO AND SAB_CODIGO = :SAB_CODIGO ";

	private static final String SELECT_BENEFICIO_VALOR_NUM_ATR = "SELECT VALOR_NUM_ATR "
			+ " valor, codigo FROM TCS_BEN_BENEFICIO_ATRIBUTO "
			+ "WHERE BOT_CODIGO = :BOT_CODIGO AND SAB_CODIGO = :SAB_CODIGO ";

	private static final String SELECT_BENEFICIO_VALOR_FEC_ATR = "SELECT TO_CHAR(VALOR_FEC_ATR,'DD/MM/RRRR') "
			+ " valor, codigo FROM TCS_BEN_BENEFICIO_ATRIBUTO "
			+ "WHERE BOT_CODIGO = :BOT_CODIGO AND SAB_CODIGO = :SAB_CODIGO ";

	private static final String SELECT_BENEFICIO_VALOR_CAR_ATR = "SELECT VALOR_CAR_ATR "
			+ " valor, codigo FROM TCS_BEN_BENEFICIO_ATRIBUTO "
			+ "WHERE BOT_CODIGO = :BOT_CODIGO AND SAB_CODIGO = :SAB_CODIGO ";

	public Collection<AtributoVO> findAtributosSubpro(String codeSubpro) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		int codeSub = Integer.parseInt(codeSubpro.trim());

		namedParameters.put("SBPR_CODIGO", codeSub);

		List<AtributoVO> lAtributos = getNamedParameterJdbcTemplate().query(
				SELECT_ATRIBUTOS_SUBPROGRAMA, namedParameters,
				new RowMapper<AtributoVO>() {

					public AtributoVO mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						AtributoVO vo = new AtributoVO();
						vo.setCodatributo(rs.getString("CODIGO"));
						vo.setNombre(rs.getString("NOMBRE"));
						vo.setTipodato(rs.getString("TIPO_DATO"));
						vo.setTipoatributo(rs.getString("TIPO_ATRIBUTO"));
						return vo;
					}
				});
		return lAtributos;
	}

	public String findSedeOtorgamientoByCodBot(String codeBot) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String sede = "";
		namedParameters.put("CODBOT", codeBot);
		try {
			sede = getNamedParameterJdbcTemplate().queryForObject(
					SELECT_SEDE_OTORGAMIENTO, namedParameters, String.class);
		} catch (IncorrectResultSizeDataAccessException e) {
			// TODO:
		}
		return sede;
	}

	public String findBeneficiarioAtri(String tipodato, String codebene,
			String codeAtri) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String consulta;
		String strValor = null;
		if (tipodato.equals("N")) {
			consulta = SELECT_BENEFICIARIO_VALOR_NUM_ATR;
		} else if (tipodato.equals("F")) {
			consulta = SELECT_BENEFICIARIO_VALOR_FEC_ATR;
		} else {
			consulta = SELECT_BENEFICIARIO_VALOR_CAR_ATR;
		}

		int codbeneficiario = Integer.parseInt(codebene.trim());
		int codatri = Integer.parseInt(codeAtri.trim());

		namedParameters.put("BEN_CODIGO", codbeneficiario);
		namedParameters.put("SAB_CODIGO", codatri);

		try {
			strValor = getNamedParameterJdbcTemplate().queryForObject(consulta,
					namedParameters, String.class);

		} catch (IncorrectResultSizeDataAccessException e) {
			// TODO:
		}
		return strValor;
	}

	public ModificarBOTVO findBeneficioAtri(String tipoDato,
			String codeBeneOtor, String codeAtri) {
		Map<String, Object> namedParameters = new HashMap<String, Object>();

		String consulta;
		if (tipoDato.equals("N")) {
			consulta = SELECT_BENEFICIO_VALOR_NUM_ATR;
		} else if (tipoDato.equals("F")) {
			consulta = SELECT_BENEFICIO_VALOR_FEC_ATR;
		} else {
			consulta = SELECT_BENEFICIO_VALOR_CAR_ATR;
		}

		int codbeneotor = Integer.parseInt(codeBeneOtor.trim());
		int codatri = Integer.parseInt(codeAtri.trim());

		ModificarBOTVO modificarBOTVO = new ModificarBOTVO();

		namedParameters.put("BOT_CODIGO", codbeneotor);
		namedParameters.put("SAB_CODIGO", codatri);

		try {
			modificarBOTVO = getNamedParameterJdbcTemplate().queryForObject(
					consulta, namedParameters, new RowMapper<ModificarBOTVO>() {

						public ModificarBOTVO mapRow(ResultSet rs, int rowNum)
								throws SQLException {
							ModificarBOTVO modificarBOTVO = new ModificarBOTVO();
							modificarBOTVO.setValorInicial(rs
									.getString("valor"));
							modificarBOTVO.setCodigo(new Long(rs
									.getLong("codigo")));
							return modificarBOTVO;
						}
					});
		} catch (IncorrectResultSizeDataAccessException e) {
			return modificarBOTVO;
		}
		return modificarBOTVO;
	}
}
